﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Configuration;

namespace Maspalomas
{
   public enum OdbcTableInfo
   {
      DatabaseName = 0,
      SchemaName = 1,
      TableName = 2,
      TableType = 3,
   }

   public class OdbcDSConnection
   {
      protected OdbcConnection m_Connection = null;
      
      public OdbcConnection Connection
      {
         get { return m_Connection; }
      }
      
      public bool WorkOffline { get; private set; }
      
      public bool OpenConnectionDefault(ILogger logger)
      {
         if (m_Connection != null)
            CloseConnection();
         
         string workOffline = ConfigurationManager.AppSettings["WorkOffline"];
         if(workOffline == "true")
         {
            WorkOffline = true;
            return false;
         }

         string connString = ConfigurationManager.ConnectionStrings["DefaultOdbcDb"].ConnectionString;

         OdbcConnection connection = new OdbcConnection(connString);

         try
         {
            connection.Open();
         }
         catch (Exception e)
         {
            connection.Dispose();
            logger.LogMessage("Wystąpił wyjątek podczas nawiązywania połączenia ze źródłem ODBC: " + e.Message);
            return false;
         }

         m_Connection = connection;
         return true;
      }

      public bool OpenConnection(string data_source, string user, string password, ILogger logger)
      {
         if (m_Connection != null)
            CloseConnection();

         OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
         builder.Dsn = data_source;
         builder.Add("Uid", user);
         builder.Add("Pwd", password);

         OdbcConnection connection = new OdbcConnection(builder.ConnectionString);

         try
         {
            connection.Open();
         }
         catch(Exception e)
         {
            connection.Dispose();
            logger.LogMessage("Wystąpił wyjątek podczas nawiązywania połączenia ze źródłem ODBC: " + e.Message);
            return false;
         }

         m_Connection = connection;
         return true;
      }

      public void CloseConnection()
      {
         if (m_Connection == null)
            return;

         m_Connection.Dispose();
         m_Connection = null;
      }

      protected bool TableExists_PG(string tableName)
      {
         string checkSql = "SELECT relname FROM pg_class WHERE relname = '{0}'";
         checkSql = string.Format(checkSql, tableName);
         DataSet ds = SqlOdbcExecution.ExecuteSelect(checkSql, m_Connection);
         if (ds.Tables[0].Rows.Count > 0)
            return true;
         else
            return false;
      }

      protected bool TableExists_ODBC(string tableName)
      {
         if (m_Connection == null)
            return false;

         try
         {
            if (m_Connection.State != ConnectionState.Open)
               return false;

            DataTable table_list = m_Connection.GetSchema("Tables");
            if (table_list == null)
               return false;

            foreach (DataRow table in table_list.Rows)
            {
               string table_name = string.Empty;
               if (!(table[(int)OdbcTableInfo.TableName] is System.DBNull) && table[(int)OdbcTableInfo.TableName] != null)
                  table_name = (string)table[(int)OdbcTableInfo.TableName];

               if (table_name == tableName)
                  return true;
            }
         }
         catch (Exception e)
         {
            throw new SqlExecutionException("Wystąpił wyjątek podczas sprawdzania istnienia tabeli w źródle ODBC: " + e.Message);
         }

         return false;
      }
   }

   public class SqlOdbcExecution
   {
      public static DataSet ExecuteSelect(string statement, OdbcConnection connection)
      {
         OdbcCommand command = new OdbcCommand(statement, connection);
         return ReadDataFromSelect(command);
      }

      protected static DataSet ReadDataFromSelect(IDbCommand command)
      {
         DataSet dataSet = new DataSet();
         try
         {
            using (IDataReader reader = command.ExecuteReader())
            {
               DataTable dt = dataSet.Tables.Add();
               for (int i = 0; i < reader.FieldCount; i++)
               {
                  DataColumn col = dt.Columns.Add();
                  col.Caption = reader.GetName(i);
                  col.DataType = reader.GetFieldType(i);
               }
               while (reader.Read())
               {
                  DataRow row = dt.NewRow();
                  for (int i = 0; i < reader.FieldCount; i++)
                     row[i] = reader[i];
                  dt.Rows.Add(row);
               }
               reader.Close();
            }
         }
         catch (Exception e)
         {
            throw new SqlExecutionException(string.Format(SqlExecutionException.c_SqlExecutionExceptionMsg, e.Message, command.CommandText));
         }
         finally
         {
         }
         return dataSet;
      }

      public static int ExecuteInsert(string statement, OdbcConnection connection)
      {
         return ExecuteNonQuery(statement, connection);
      }

      public static int ExecuteUpdate(string statement, OdbcConnection connection)
      {
         return SqlOdbcExecution.ExecuteNonQuery(statement, connection);
      }

      public static int ExecuteDelete(string statement, OdbcConnection connection)
      {
         return ExecuteNonQuery(statement, connection);
      }

      public static int ExecuteNonQuery(string statement, OdbcConnection connection)
      {
         int Affected = 0;
         OdbcCommand Command = new OdbcCommand(statement, connection);
         try
         {
            Affected = Command.ExecuteNonQuery();
         }
         catch (Exception e)
         {
            throw new SqlExecutionException(string.Format(SqlExecutionException.c_SqlExecutionExceptionMsg, e.Message, statement));
         }
         finally
         {
            Command.Dispose();
         }
         return Affected;
      }
   }

   public class SqlExecutionException : Exception
   {
      public const string c_SqlExecutionExceptionMsg = "SQL Execution exception '{0}' while executing statement '{1}'";

      public SqlExecutionException(string message) : base(message)
      {
      }
   }
}
